# This file is used to create the user_info_ext table in the database.

'''
-- 接收RFID工具箱信息表
CREATE TABLE IF NOT EXISTS receive_rfid_tool_box_info(
    id SERIAL PRIMARY KEY,
    mac VARCHAR(20) NOT NULL,
    task_type VARCHAR(20) NOT NULL,
    task_sn VARCHAR(50) NOT NULL,
    task_data json NOT NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP    
);
'''
import json

class ReceiveRfidToolBoxInfoTableMethods: # 接收RFID工具箱信息表方法类

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn

    def insert_rfid_tool_box_info(self, mac, task_type, task_sn, task_data):
        sql = "INSERT INTO receive_rfid_tool_box_info (mac, task_type, task_sn, task_data) VALUES (%s, %s, %s, %s)"
        params = (mac, task_type, task_sn, task_data)
        self.cursor.execute(sql, params)
        self.conn.commit()

    # 查询RFID工具箱信息
    def select_rfid_tool_box_info(self, mac, limit_num=10, page_num=1):
        sql = "SELECT * FROM receive_rfid_tool_box_info WHERE mac = %s ORDER BY create_time DESC LIMIT %s OFFSET %s"    
        params = (mac, limit_num, (page_num-1)*limit_num)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchall()
        return result
    
    # 通过task_sn查询RFID工具箱信息
    def select_rfid_tool_box_info_by_task_sn(self, task_sn):
        sql = "SELECT * FROM receive_rfid_tool_box_info WHERE task_sn = %s"
        params = (task_sn,)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchall()
        return result
    

    # 删除RFID工具箱信息
    def delete_rfid_tool_box_info(self, id):
        sql = "DELETE FROM receive_rfid_tool_box_info WHERE id = %s"
        params = (id,)
        self.cursor.execute(sql, params)
        self.conn.commit()


    # 根据task_type和mac查询RFID工具箱信息
    def select_rfid_tool_box_info_by_task_type_and_mac(self, task_type, mac, limit_num=10, page_num=1, order_by="id", order_type="DESC"):
        sql = "SELECT * FROM receive_rfid_tool_box_info WHERE task_type = %s AND mac = %s ORDER BY %s %s LIMIT %s OFFSET %s"
        params = (task_type, mac, order_by, order_type, limit_num, (page_num-1)*limit_num)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchall()
        return result
    
    # 根据mac查询最新的RFID工具箱GPS坐标信息
    def get_task_type_is_GPS_by_mac_last(self, mac):
        sql = "SELECT * FROM receive_rfid_tool_box_info WHERE mac = %s AND task_type='GPS' ORDER BY create_time DESC LIMIT 1"
        params = (mac,)
        self.cursor.execute(sql, params)
        result = self.cursor.fetchone()

        return result
    
    def get_task_type_is_inventory_data_by_mac_last_time(self, mac):
        '''inventory_data 有时会分多个包，需要取多个包的数据拼接起来'''

        sql = "SELECT * FROM receive_rfid_tool_box_info WHERE mac = %s AND task_type='inventory_data' ORDER BY create_time DESC LIMIT 1"

        self.cursor.execute(sql, (mac,))

        result = self.cursor.fetchone()

        ''': result :{
            "task_type": "inventory_data",
            "mac": "xxxxxxxx", # 设备唯一标识码
            "task_sn": "yyyyyyyy", # 任务唯一标识码
            "task_data": {
                "tag_list": ["000001", "000002","000003","000004","000005","000006"],
                "data_num": "1/5"
            }
        }'''

        if not result:

            return []
        
        # print(result)

        task_data = result['task_data']

        data_num = task_data.get('data_num')

        if data_num is None:

            return task_data["tag_list"]
        
        task_sn = result['task_sn']

        task_list =self.select_rfid_tool_box_info_by_task_sn(task_sn)
        
        if len(task_list) != int(data_num[-1]):

            return "数据包数量不匹配"
        
        tag_list = []

        for task in task_list:

            tag_list.extend(task['task_data']['tag_list'])

        return tag_list
        # return [tag for task in task_list for tag in task['task_data']['tag_list']]
    


        
    

    